﻿using FarPoint.Win;
using FarPoint.Win.Chart;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.CellType;
using FarPoint.Win.Spread.Chart;
using FarPoint.Win.Spread.Model;
using GrapeCity.StudentDashboard.CellTypes;
using GrapeCity.StudentDashboard.Sparkline;
using Spread.Common.DataStore;
using Spread.Common.DataStore.DataSourceEntities;
using Spread.Common.DataStore.Models;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;

namespace GrapeCity.StudentDashboard
{
    public partial class StudentDashboardControl : Spread.Common.Features.Sample.BaseSampleControl
    {
        #region Private variables
        private StudentDashboardDataSource _dataSource;
        private List<Student> _students;
        private List<AbsentRecords> _allAbsentRecords;
        private List<TardyRecords> _allTardyRecords;
        private List<ScoreRecords> _allScoreRecords;
        List<TermSession> _termSessions;
        private List<AbsentRecord> _absentRecords;
        private List<TardyRecord> _tardyRecords;
        private List<ScoreRecord> _scoreRecords;

        private DateTime _termStartDate;
        private DateTime _termEndDate;

        private TimelineCellType _timelineCell;
        #endregion

        #region ctor
        public StudentDashboardControl()
        {
            InitializeComponent();

            _dataSource = new DataService().StudentDashboardDataSource;
            _students = _dataSource.GetAllStudents();
            _allAbsentRecords = _dataSource.GetAllAbsentRecords();
            _allTardyRecords = _dataSource.GetAllTardyRecords();
            _allScoreRecords = _dataSource.GetAllScoreRecords();
            _termSessions = _dataSource.GetAllTermSessions();

            Load += StudentDashboardControl_Load;
        }
        #endregion

        #region Event handlers
        private void StudentDashboardControl_Load(object sender, EventArgs e)
        {
            //fpSpread.ActiveSheet.OpenExcel(@"Template\Dashboard.xlsx", 0);
            fpSpread.OpenExcel(@"Template\Dashboard.xlsx");
            fpSpread.ActiveSheet.RowCount = _students.Count + 17;
            fpSpread.ActiveSheet.ColumnCount = 20;

            ClearData(fpSpread.ActiveSheet);
            fpSpread.Sheets.Add(new SheetView("Data"));
            fpSpread.Sheets.Add(new SheetView("Attendance"));

            fpSpread.SuspendLayout();

            AddComboSelector(fpSpread.Sheets[0], 1, 2);

            UpdateRecords();
            SetCellTypes(fpSpread.Sheets[0]);
            SetStudentDataSheetProperties(fpSpread.Sheets["Data"]);
            LoadStudentAttendance(fpSpread.Sheets["Attendance"]);
            LoadStudentInfo(fpSpread.Sheets[0], 5, 2);
            LoadStudentData(fpSpread.Sheets["Data"]);

            fpSpread.ActiveSheet.Cells[43, 6, 45, 10].Font = new Font("Segoe", 12, FontStyle.Regular);
            fpSpread.ActiveSheet.Cells[43, 6, 45, 10].HorizontalAlignment = CellHorizontalAlignment.Center;
            fpSpread.ActiveSheet.AddSpanCell(43, 6, 1, 5);
            fpSpread.ActiveSheet.Cells[43, 6].Text = "Course Grade Distribution";
            fpSpread.ActiveSheet.Cells[44, 6].ForeColor = Color.Red;
            fpSpread.ActiveSheet.Cells[44, 6].Text = "F";
            fpSpread.ActiveSheet.Cells[44, 7, 44, 8].ForeColor = Color.Orange;
            fpSpread.ActiveSheet.Cells[44, 7].Text = "D";
            fpSpread.ActiveSheet.Cells[44, 8].Text = "C";
            fpSpread.ActiveSheet.Cells[44, 9, 44, 10].ForeColor = Color.Green;
            fpSpread.ActiveSheet.Cells[44, 9].Text = "B";
            fpSpread.ActiveSheet.Cells[44, 10].Text = "A";
            fpSpread.ActiveSheet.Columns[0].Width = 0;
            fpSpread.ActiveSheet.Rows[0].Height = 0;

            fpSpread.ResumeLayout(true);
        }

        private void ComboCell_EditingStopped(object sender, EventArgs e)
        {
            UpdateRecords();

            fpSpread.SuspendLayout();

            SetStudentDataSheetProperties(fpSpread.Sheets["Data"]);
            LoadStudentAttendance(fpSpread.Sheets["Attendance"]);
            LoadStudentInfo(fpSpread.Sheets[0], 5, 2);
            LoadStudentData(fpSpread.Sheets["Data"]);

            fpSpread.ResumeLayout(true);
        }

        // Prevent resizing and moving of PlotArea for Chart
        private void Chart_ActionDoing(object sender, CancelEventArgs e)
        {
            e.Cancel = true;
        }
        #endregion

        #region Private methods
        // Add Combobox selector for Term selection
        private void AddComboSelector(SheetView sheet, int row, int col)
        {
            List<string> items = new List<string>();
            for (int year = DateTime.Now.Year - 2; year <= DateTime.Now.Year; ++year)
            {
                items.Add($"Term 1, {year}");
                if (year == DateTime.Now.Year && DateTime.Now.Month < 7)
                    break;
                items.Add($"Term 2, {year}");
            }

            ComboBoxCellType comboCell = new ComboBoxCellType
            {
                DropDownOptions = DropDownOptions.ButtonAndText,
                Editable = false,
                StopEditingAfterDropDownItemSelected = true,
                Items = items.ToArray()
            };
            comboCell.EditingStopped += ComboCell_EditingStopped;

            sheet.Cells[row, col].ResetValue();
            sheet.Cells[row, col].CellType = comboCell;
            sheet.Cells[row, col].Value = comboCell.Items.Last();
            sheet.Cells[row, col].Font = new Font("Segoe UI", 14, FontStyle.Regular);
            sheet.Cells[row, col].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[row, col].VerticalAlignment = CellVerticalAlignment.Center;
            sheet.Cells[row, col].Tag = "TermComboCell";
        }

        // Update Record variable according to Combobox selection
        private void UpdateRecords()
        {
            string text = fpSpread.Sheets[0].Cells["TermComboCell"].Text;
            int term = int.Parse(text.Substring(5, 1));
            int year = int.Parse(text.Substring(8, 4));

            if (term == 1)
            {
                _termStartDate = new DateTime(year, 1, 1);
                _termEndDate = new DateTime(year, 6, 30);

            }
            else
            {
                _termStartDate = new DateTime(year, 7, 1);
                _termEndDate = new DateTime(year, 12, 31);
            }

            if (_timelineCell != null)
            {
                _timelineCell.StartDate = _termStartDate;
                _timelineCell.EndDate = _termEndDate;
            }
            DateTime endDate = _termEndDate;

            // For current year, retrieve data only upto current date
            if (year == DateTime.Now.Year)
                endDate = DateTime.Compare(DateTime.Now, _termEndDate) < 0 ? DateTime.Now : _termEndDate;
            int sessionID = _termSessions.Where(session => session.TermNumber == term && session.Year == year).
                First().SessionID;

            _absentRecords = _allAbsentRecords.Where(r => r.SessionID == sessionID).First().StudentRecords.
                Where(r => r.Date <= endDate).ToList();
            _tardyRecords = _allTardyRecords.Where(r => r.SessionID == sessionID).First().StudentRecords.
                Where(r => r.Date <= endDate).ToList();
            _scoreRecords = _allScoreRecords.Where(r => r.SessionID == sessionID).First().StudentRecords;
        }

        // Set cell types for Main sheet
        private void SetCellTypes(SheetView sheet)
        {
            _timelineCell = new TimelineCellType(_termStartDate, _termEndDate);
            sheet.Cells[4, 14].CellType = _timelineCell;
            sheet.Rows[4].Height = 30;
        }

        // Load Attendance data in "Attendance" sheet
        private void LoadStudentAttendance(SheetView sheet)
        {
            sheet.Reset();
            sheet.SheetName = "Attendance";
            sheet.RowCount = _students.Count * 2;

            sheet.Columns[0, 1].Locked = true;

            sheet.Rows[0, sheet.RowCount - 1].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Rows[0, sheet.RowCount - 1].VerticalAlignment = CellVerticalAlignment.Center;
            sheet.Columns[0, sheet.ColumnCount - 1].Width = 100;

            int maxColIndex = -1;
            for (int i = 0; i < _students.Count; ++i)
            {
                sheet.AddSpanCell(2 * i, 0, 2, 1);
                sheet.Cells[2 * i, 0].Text = _students[i].Name;
                sheet.Cells[2 * i, 1].Text = "Absent";
                sheet.Cells[2 * i + 1, 1].Text = "Tardy";

                int col = 2;
                foreach (var record in _absentRecords.Where(r => r.StudentID == _students[i].ID))
                {
                    if (col > maxColIndex)
                        maxColIndex = col;
                    sheet.Cells[2 * i, col++].Value = record.Date;
                }

                col = 2;
                foreach (var record in _tardyRecords.Where(r => r.StudentID == _students[i].ID))
                {
                    if (col > maxColIndex)
                        maxColIndex = col;
                    sheet.Cells[2 * i + 1, col++].Value = record.Date;
                }
            }
            sheet.ColumnCount = maxColIndex + 1;

            DateTimeCellType dateCell = new DateTimeCellType
            {
                DateSeparator = "-",
                DateTimeFormat = DateTimeFormat.ShortDate,
                DropDownButton = false
            };

            sheet.Cells[0, 2, sheet.RowCount - 1, sheet.ColumnCount - 1].CellType = dateCell;

            // Borders
            for (int i = 0; i < sheet.RowCount; ++i)
            {
                sheet.Cells[i, 1].Border = new LineBorder(Color.Gray, 1, false, false, true, true);
                sheet.Cells[i, 2, i, sheet.ColumnCount - 1].Border = new LineBorder(Color.Gray, 1, false, false, false,
                    true);
                if (i % 2 == 0)
                    sheet.Cells[i, 0].Border = new LineBorder(Color.Gray, 2, false, false, true, true);
                else
                {
                    sheet.Cells[i, 1].Border = new ComplexBorder(null, null, new ComplexBorderSide(Color.Gray, 1),
                        new ComplexBorderSide(Color.Gray, 2));
                    sheet.Cells[i, 2, i, sheet.ColumnCount - 1].Border = new LineBorder(Color.Gray, 2, false, false, false, true);
                }
            }
        }

        // Clear Sparklines and data from template
        private void ClearData(SheetView sheet)
        {
            sheet.SparklineContainer.Clear();
            sheet.Cells[5, 1, 35, 18].ResetValue();
        }

        // Set sheet properties for "Data" sheet
        private void SetStudentDataSheetProperties(SheetView sheet)
        {
            sheet.Reset();
            sheet.SheetName = "Data";
            sheet.RowCount = _students.Count + 5;
            sheet.ColumnCount = 26;

            sheet.Rows[1, 2].Font = new Font("Segoe UI", 10, FontStyle.Bold);
            sheet.Columns[1, sheet.ColumnCount - 1].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Columns[0, sheet.ColumnCount - 1].VerticalAlignment = CellVerticalAlignment.Center;

            // Student Names
            sheet.Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[1, 0].Border = new LineBorder(Color.Gray, 2, true, true, true, true);
            sheet.Cells[3, 0, _students.Count + 1, 0].Border = new LineBorder(Color.Gray, 2, true, false, true,
                false);
            sheet.Cells[_students.Count + 2, 0].Border = new LineBorder(Color.Gray, 2, true, false, true, true);

            // Late Assignment Count
            sheet.Columns[8].Width = 140;

            // Assignment Border
            sheet.Cells[1, 3, 1, 7].Border = new LineBorder(Color.Gray, 2, false, true, false, false);
            sheet.Cells[1, 8].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 1),
                new ComplexBorderSide(Color.Gray, 2), new ComplexBorderSide(Color.Gray, 2),
                new ComplexBorderSide(Color.Gray, 1));
            sheet.Cells[2, 3, 2, 7].Border = new LineBorder(Color.Gray, 1, false, false, false, true);
            sheet.Cells[2, 8, _students.Count + 1, 8].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 1),
                null, new ComplexBorderSide(Color.Gray, 2), null);
            sheet.Cells[_students.Count + 2, 3, _students.Count + 2, 7].Border = new LineBorder(Color.Gray, 2, false, false,
                false, true);
            sheet.Cells[_students.Count + 2, 8].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 1),
                null, new ComplexBorderSide(Color.Gray, 2), new ComplexBorderSide(Color.Gray, 2));

            // Assessment Border
            sheet.Cells[1, 10].Border = new LineBorder(Color.Gray, 2, true, true, true, false);
            sheet.Cells[3, 10, _students.Count + 1, 10].Border = new LineBorder(Color.Gray, 2, true, false, false, false);
            sheet.Cells[2, 11, 2, 13].Border = new LineBorder(Color.Gray, 1, false, false, false, true);
            sheet.Cells[3, 14, _students.Count + 1, 14].Border = new LineBorder(Color.Gray, 2, false, false, true,
                false);
            sheet.Cells[_students.Count + 2, 11, _students.Count + 2, 13].Border = new LineBorder(Color.Gray, 2,
                false, false, false, true);

            sheet.Cells[2, 10].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 2), null, null,
                new ComplexBorderSide(Color.Gray, 1));
            sheet.Cells[2, 14].Border = new ComplexBorder(null, null, new ComplexBorderSide(Color.Gray, 2),
                new ComplexBorderSide(Color.Gray, 1));
            sheet.Cells[_students.Count + 2, 14].Border = new LineBorder(Color.Gray, 2, false, false, true, true);
            sheet.Cells[_students.Count + 2, 10].Border = new LineBorder(Color.Gray, 2, true, false, false, true);

            // Grades
            sheet.Columns[16, 19].Width = 130;

            // Grades Border
            sheet.Cells[1, 16].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 2),
                new ComplexBorderSide(Color.Gray, 2), null, new ComplexBorderSide(Color.Gray, 1));
            sheet.Cells[1, 17, 1, 18].Border = new ComplexBorder(null, new ComplexBorderSide(Color.Gray, 2),
                null, new ComplexBorderSide(Color.Gray, 1));
            sheet.Cells[1, 19].Border = new ComplexBorder(null, new ComplexBorderSide(Color.Gray, 2),
                new ComplexBorderSide(Color.Gray, 2), new ComplexBorderSide(Color.Gray, 1));

            sheet.Cells[3, 16, _students.Count + 1, 16].Border = new LineBorder(Color.Gray, 2, true, false, false, false);
            sheet.Cells[3, 19, _students.Count + 1, 19].Border = new LineBorder(Color.Gray, 2, false, false, true, false);
            sheet.Cells[_students.Count + 2, 17, _students.Count + 2, 18].Border = new LineBorder(Color.Gray, 2, false,
                false, false, true);
            sheet.Cells[_students.Count + 2, 16].Border = new LineBorder(Color.Gray, 2, true, false, false, true);
            sheet.Cells[_students.Count + 2, 19].Border = new LineBorder(Color.Gray, 2, false, false, true, true);

            // Referral & Detention Count
            sheet.Columns[21, 24].Width = 100;

            // Referral & Detention Border
            sheet.Cells[1, 21].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 2),
                new ComplexBorderSide(Color.Gray, 2), new ComplexBorderSide(Color.Gray, 1), null);
            sheet.Cells[2, 21].Border = new ComplexBorder(new ComplexBorderSide(Color.Gray, 2), null, null,
                new ComplexBorderSide(Color.Gray, 1));
            sheet.Cells[2, 22].Border = new LineBorder(Color.Gray, 1, false, false, true, true);
            sheet.Cells[1, 23].Border = new LineBorder(Color.Gray, 2, false, true, true, false);
            sheet.Cells[2, 23].Border = new LineBorder(Color.Gray, 1, false, false, false, true);
            sheet.Cells[2, 24].Border = new ComplexBorder(null, null, new ComplexBorderSide(Color.Gray, 2),
                new ComplexBorderSide(Color.Gray, 1));

            sheet.Cells[3, 21, _students.Count + 1, 21].Border = new LineBorder(Color.Gray, 2, true, false, false,
                false);
            sheet.Cells[3, 22, _students.Count + 1, 22].Border = new LineBorder(Color.Gray, 1, false, false, true,
                false);
            sheet.Cells[3, 24, _students.Count + 1, 24].Border = new LineBorder(Color.Gray, 2, false, false, true,
                false);
            sheet.Cells[_students.Count + 2, 21].Border = new LineBorder(Color.Gray, 2, true, false, false, true);
            sheet.Cells[_students.Count + 2, 22].Border = new ComplexBorder(null, null,
                new ComplexBorderSide(Color.Gray, 1), new ComplexBorderSide(Color.Gray, 2));
            sheet.Cells[_students.Count + 2, 23].Border = new LineBorder(Color.Gray, 2, false, false, false, true);
            sheet.Cells[_students.Count + 2, 24].Border = new LineBorder(Color.Gray, 2, false, false, true, true);

            // Lock headers
            sheet.Cells[1, 0, _students.Count + 2, 0].Locked = true;
            sheet.Cells[1, 3, 2, 8].Locked = true;
            sheet.Cells[1, 10, 2, 14].Locked = true;
            sheet.Cells[1, 16, 1, 19].Locked = true;
            sheet.Cells[1, 21, 2, 24].Locked = true;
        }

        // Load raw Student stats in "Data" sheet
        private void LoadStudentData(SheetView sheet)
        {
            sheet.AddSpanCell(1, 0, 2, 3);
            sheet.Cells[1, 0].Text = "Student";

            sheet.AddSpanCell(1, 3, 1, 5);
            sheet.Cells[1, 3].Text = "Assignments";

            sheet.SetClipValue(2, 3, 1, 5, "1\t2\t3\t4\t5");

            sheet.AddSpanCell(1, 8, 2, 1);
            sheet.Cells[1, 8].Text = "Late Assignments";

            sheet.AddSpanCell(1, 10, 1, 5);
            sheet.Cells[1, 10].Text = "Assessments";
            sheet.Cells[1, 10, 2, 14].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[1, 10, 2, 14].Font = new Font("Segoe UI", 10, FontStyle.Bold);

            sheet.SetClipValue(2, 10, 1, 5, "1\t2\t3\t4\t5");

            sheet.AddSpanCell(1, 16, 2, 1);
            sheet.AddSpanCell(1, 17, 2, 1);
            sheet.AddSpanCell(1, 18, 2, 1);
            sheet.AddSpanCell(1, 19, 2, 1);
            sheet.Cells[1, 16].Text = "Current Score";
            sheet.Cells[1, 17].Text = "Current Grade";
            sheet.Cells[1, 18].Text = "Target Score";
            sheet.Cells[1, 19].Text = "Previous Term Score";

            sheet.AddSpanCell(1, 21, 1, 2);
            sheet.AddSpanCell(1, 23, 1, 2);
            sheet.Cells[1, 21].Text = "Referrals";
            sheet.Cells[1, 23].Text = "Detentions";
            sheet.SetClip(2, 21, 1, 4, "Previous Term\tCurrent Term\tPrevious Term\tCurrent Term");

            for (int i = 0; i < _students.Count; ++i)
            {
                sheet.AddSpanCell(i + 3, 0, 1, 3);
                sheet.Cells[i + 3, 0].Text = _students[i].Name;

                ScoreRecord studentRecord = _scoreRecords.Where(r => r.StudentID == _students[i].ID).FirstOrDefault();
                for (int j = 0; j < studentRecord.AssignmentScores.Count; ++j)
                {
                    sheet.Cells[i + 3, j + 3].Value = studentRecord.AssignmentScores[j];
                    sheet.Cells[i + 3, j + 10].Value = studentRecord.AssessmentScores[j];
                }

                sheet.Cells[i + 3, 8].Value = studentRecord.LateAssignmentCount == 0 ?
                    string.Empty : studentRecord.LateAssignmentCount.ToString();

                sheet.Cells[i + 3, 21].Value = studentRecord.PreviousTermReferralCount == 0 ?
                    string.Empty : studentRecord.PreviousTermReferralCount.ToString();
                sheet.Cells[i + 3, 22].Value = studentRecord.CurrentTermReferralCount == 0 ?
                    string.Empty : studentRecord.CurrentTermReferralCount.ToString();
                sheet.Cells[i + 3, 23].Value = studentRecord.PreviousTermDetentionCount == 0 ?
                    string.Empty : studentRecord.PreviousTermDetentionCount.ToString();
                sheet.Cells[i + 3, 24].Value = studentRecord.CurrentTermDetentionCount == 0 ?
                    string.Empty : studentRecord.CurrentTermDetentionCount.ToString();

                sheet.Cells[i + 3, 16].CellType = new NumberCellType()
                {
                    DecimalPlaces = 0
                };
                // Course Score = Average(Assignemnt Scores, Assessment Scores)
                sheet.Cells[i + 3, 16].Formula = $"AVERAGE(Data!$D${i + 4}:$H${i + 4}, Data!$J${i + 4}:" +
                    $"$N${i + 4})";

                // Course Grade Scheme:
                // Score >= 90 --> 'A'
                // Score >= 80 --> 'B'
                // Score => 70 --> 'C'
                // Score => 60 --> 'D'
                // Score < 60  --> 'F'
                sheet.Cells[i + 3, 17].Formula = $"IF($Q${i + 4} >= 90, \"A\", IF($Q${i + 4} >= 80, \"B\", " +
                    $"IF($Q${i + 4} >= 70, \"C\", IF($Q${i + 4} >= 60, \"D\", \"F\"))))";
                sheet.Cells[i + 3, 18].Value = studentRecord.TargetCourseScore;
                sheet.Cells[i + 3, 19].Value = studentRecord.PreviousCourseScore;
            }
        }

        // Load Student Stats and Sparklines from data sheets
        private void LoadStudentInfo(SheetView sheet, int row, int col)
        {
            sheet.Cells["TermComboCell"].Locked = false;

            for (int i = 0; i < _students.Count; ++i)
            {
                sheet.Cells[row + i, col].Value = _students[i].Name;
                sheet.Cells[row + i, col + 1].Value = _students[i].EnglishLanguageDeficient ? "E" : string.Empty;
                sheet.Cells[row + i, col + 2].Value = _students[i].SpecialEdStudent ? "S" : string.Empty;

                sheet.Cells[row + i, col + 5].Formula = $"Data!$Q${i + 4}";
                sheet.Cells[row + i, col + 3].Formula = $"Data!$R${i + 4}";
                sheet.Cells[row + i, col + 11].Formula = $"Data!$N${i + 4}";

                // Overall Course Grade Sparkline
                BulletSparkline sparkline = new BulletSparkline(sheet, row + i, 6,
                    new SheetCellRange(sheet, row + i, 7, 1, 1), new SheetCellRange(fpSpread.Sheets["Data"],
                    i + 3, 18, 1, 1));
                BulletSparklineGroup group = new BulletSparklineGroup();
                BulletSparklineSetting setting = new BulletSparklineSetting(0, Color.FromArgb(244, 232, 217), false);
                sheet.SparklineContainer.Add(group);
                group.Setting = setting;
                group.Renderer = new BulletSparklineRenderer(100, 90, 60, true);
                group.Add(sparkline);

                // Spread Sparkline
                AddAssignmentSparkline(sheet, row + i, 9, new SheetCellRange(fpSpread.Sheets["Data"], i + 3, 3, 1, 5),
                    new DistributionSparklineRenderer());

                // Last Trend Sparkline
                sparkline = new BulletSparkline(sheet, row + i, 10, new SheetCellRange(sheet, row + i, 7, 1, 1),
                    new SheetCellRange(fpSpread.Sheets["Data"], i + 3, 3, 1, 5));
                group = new BulletSparklineGroup();
                sheet.SparklineContainer.Add(group);
                group.Setting = setting;
                group.Renderer = new BulletSparklineRenderer(100, 90, 60);
                group.Add(sparkline);

                // YTD Sparkline
                sheet.AddSparkline(new SheetCellRange(fpSpread.Sheets["Data"], i + 3, 3, 1, 5),
                    new CellRange(row + i, col + 6, 1, 1), SparklineType.Line, new ExcelSparklineSetting());

                // Late Assignment Count
                sheet.Cells[row + i, col + 9].Formula = $"Data!$I${i + 4}";

                // Last 5 Assessments
                sheet.AddSparkline(new SheetCellRange(fpSpread.Sheets["Data"], i + 3, 9, 1, 5),
                    new CellRange(row + i, col + 10, 1, 1), SparklineType.Line, new ExcelSparklineSetting());

                // Referral & Detention Counts
                sheet.Cells[row + i, col + 13].Formula = $"Data!$V${i + 4}";
                sheet.Cells[row + i, col + 14].Formula = $"Data!$W${i + 4}";
                sheet.Cells[row + i, col + 15].Formula = $"Data!$X${i + 4}";
                sheet.Cells[row + i, col + 16].Formula = $"Data!$Y${i + 4}";

                AddAttendanceSparkline(sheet, row + i, 14);
            }
            AddCharts(sheet);
        }

        // Add Charts for Absence and Tardiness
        private void AddCharts(SheetView sheet)
        {
            sheet.Charts.Clear();

            sheet.Rows[35, 42].Height = 30;

            sheet.Cells[37, 14].ResetText();
            sheet.Cells[41, 14].ResetText();

            Font font = new Font("Segoe UI", 11, FontStyle.Bold);
            sheet.AddSpanCell(36, 12, 2, 2);
            sheet.Cells[36, 12].Font = font;
            sheet.Cells[36, 12].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[36, 12].VerticalAlignment = CellVerticalAlignment.Center;
            sheet.Cells[36, 12].ResetForeColor();
            sheet.Cells[36, 12].ForeColor = Color.DarkOrange;
            sheet.Cells[36, 12].Text = "Absences";

            sheet.AddSpanCell(40, 12, 2, 2);
            sheet.Cells[40, 12].Font = font;
            sheet.Cells[40, 12].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[40, 12].VerticalAlignment = CellVerticalAlignment.Center;
            sheet.Cells[40, 12].ResetForeColor();
            sheet.Cells[40, 12].ForeColor = Color.DodgerBlue;
            sheet.Cells[40, 12].Text = "Tardiness";

            AddChart(sheet, 35, 14, 38, 14, _absentRecords.Select(r => (IStudentRecord)r).ToList(), Color.DarkOrange);
            AddChart(sheet, 39, 14, 42, 14, _tardyRecords.Select(r => (IStudentRecord)r).ToList(), Color.DodgerBlue);
        }

        // Add a Chart
        private void AddChart(SheetView sheet, int start_row, int start_col, int end_row, int end_col,
            List<IStudentRecord> record, Color fillColor)
        {
            int width = -1, height = -1;
            Point location = GetCellRangeLocation(sheet, start_row, start_col, end_row, end_col, out width,
                out height);
            SpreadChart chart = new SpreadChart();
            chart.CanMove = FarPoint.Win.Spread.DrawingSpace.Moving.None;
            chart.CanSize = FarPoint.Win.Spread.DrawingSpace.Sizing.None;
            chart.ActionDoing += Chart_ActionDoing;
            chart.Size = new Size(width, height);
            chart.Location = location;

            ChartModel model = new ChartModel();
            YPlotArea plotArea = new YPlotArea();
            plotArea.YAxes[0].MajorGridVisible = false;
            plotArea.YAxes[0].AutoMajorUnit = false;
            plotArea.YAxes[0].AutoMinimum = false;
            plotArea.YAxes[0].Minimum = 0;
            plotArea.YAxes[0].MajorUnit = 1;

            plotArea.XAxis.LabelVisible = false;
            plotArea.XAxis.MajorGridVisible = false;
            plotArea.XAxis.MajorTickVisible = false;
            LineSeries series = new LineSeries();
            series.PointMarker = new BuiltinMarker(MarkerShape.Dot, 0.2f);
            series.PointFill = new SolidFill(fillColor);
            series.SmoothedLine = true;
            series.LineBorder = new SolidLine(fillColor, 0.5f);

            DateTime date = _termStartDate;
            DateTime endDate = _termEndDate;

            while (date <= endDate)
            {
                int count = record.Count(r => r.Date == date);
                series.Values.Add(record.Count(r => r.Date == date));
                date = date.AddDays(1);
            }
            plotArea.Series.Add(series);
            model.PlotAreas.Add(plotArea);
            chart.Model = model;

            sheet.Charts.Add(chart);
        }

        // Get location and size for a Chart
        private Point GetCellRangeLocation(SheetView sheet, int start_row, int start_col, int end_row, int end_col,
            out int width, out int height)
        {
            float x = 0, y = 0;
            for (int i = 0; i < start_row; ++i)
            {
                y += sheet.Rows[i].Height;
            }
            for (int i = 0; i < start_col; ++i)
            {
                x += sheet.Columns[i].Width;
            }

            float w = 0, h = 0;
            for (int i = start_row; i <= end_row; ++i)
            {
                h += sheet.Rows[i].Height;
            }
            for (int i = start_col; i <= end_col; ++i)
            {
                w += sheet.Columns[i].Width;
            }
            width = (int)w - 5;
            height = (int)h - 5;

            return new Point((int)x + 2, (int)y + 2);
        }

        // Add Assignment sparkline
        private void AddAssignmentSparkline(SheetView sheet, int row, int col, SheetCellRange cellRange,
            ISparklineRenderer renderer)
        {
            DistributionSparkline sparkline = new DistributionSparkline(sheet, row, col, cellRange);
            DistributionSparklineGroup group = new DistributionSparklineGroup();
            sheet.SparklineContainer.Add(group);
            group.Setting = new DistributionSparklineSetting();
            group.Renderer = renderer;
            group.Add(sparkline);
        }

        // Add Attendance sparkline
        private void AddAttendanceSparkline(SheetView sheet, int row, int col)
        {
            string studentName = sheet.Cells[row, 2].Value as string;
            int rowIndex = GetAttendanceRowIndexForStudent(studentName);
            int studentID = _students.Where(stu => stu.Name == studentName).First().ID;

            int absentCount = _absentRecords.Where(r => r.StudentID == studentID).Count();
            int tardyCount = _tardyRecords.Where(r => r.StudentID == studentID).Count();

            DistributionSparkline sparkline = new DistributionSparkline(sheet, row, col,
                new SheetCellRange(fpSpread.Sheets["Attendance"], rowIndex, 2, 1, absentCount),
                new SheetCellRange(fpSpread.Sheets["Attendance"], rowIndex + 1, 2, 1, tardyCount));
            DistributionSparklineGroup group = new DistributionSparklineGroup();
            sheet.SparklineContainer.Add(group);
            group.Setting = new DistributionSparklineSetting();
            group.Renderer = new AttendanceSparklineRenderer(_termStartDate, _termEndDate);
            group.Add(sparkline);
        }

        // Get row index of a given Student in "Attendance" sheet
        private int GetAttendanceRowIndexForStudent(string studentName)
        {
            SheetView sheet = fpSpread.Sheets["Attendance"];

            for (int i = 0; i < _students.Count; ++i)
            {
                if (sheet.Cells[2 * i, 0].Text == studentName)
                    return i;
            }
            return -1;
        }
        #endregion
    }
}


//
//StudentDashboardControl.Designer.cs
//


namespace GrapeCity.StudentDashboard
{
    partial class StudentDashboardControl
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this.fpSpread_Sheet1 = new FarPoint.Win.Spread.SheetView();
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            // 
            // fpSpread
            // 
            this.fpSpread.AccessibleDescription = "";
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this.fpSpread_Sheet1});
            this.fpSpread.Size = new System.Drawing.Size(477, 487);
            this.fpSpread.TabIndex = 0;
            // 
            // fpSpread_Sheet1
            // 
            this.fpSpread_Sheet1.Reset();
            this.fpSpread_Sheet1.SheetName = "Sheet1";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.fpSpread_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // StudentDashboardControl
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "StudentDashboardControl";
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView fpSpread_Sheet1;
    }
}


//
//CellTypes\TimelineCellType.cs
//


using FarPoint.Win.Spread.CellType;
using System;
using FarPoint.Win.Spread;
using System.Drawing;

namespace GrapeCity.StudentDashboard.CellTypes
{
    /// <summary>
    /// Celltype to draw Monthly Attendance timeline of a Term
    /// eg. For Term 1, 2018
    ///   Jan      Feb     Mar     Apr     May     Jun
    /// -------------------------------------------------
    /// |       |       |       |       |       |       |
    /// </summary>
    public class TimelineCellType : EditBaseCellType
    {
        #region Properties
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }
        #endregion

        #region ctor
        public TimelineCellType(DateTime startDate, DateTime endDate)
        {
            StartDate = startDate;
            EndDate = endDate;
        }
        #endregion

        #region Public methods
        public override void PaintCell(Graphics g, Rectangle r, Appearance appearance, object value, bool isSelected, bool isLocked, float zoomFactor)
        {
            float x1 = r.X + 0.05f * r.Width;
            float y = r.Y + 0.65f * r.Height;
            float totalWidth = 0.9f * r.Width;
            float totalHeight = 0.3f * r.Height;
            Color color = Color.FromArgb(180, 180, 180);

            // Draw a straight line across the cell at the center
            g.FillRectangle(new SolidBrush(color), new RectangleF(x1, y, totalWidth, 1));

            // Draw a vertical straight line from center to bottom of cell at the start of above line
            g.FillRectangle(new SolidBrush(color), new RectangleF(x1, y, 1, totalHeight));

            int totalDays = (int)EndDate.Subtract(StartDate).TotalDays;

            DateTime date = StartDate;
            // Draw a Date String (MMM) above the horizontal line with its width proportional to days in that Month
            while (date < EndDate)
            {
                DateTime nextDate = date.AddMonths(1);
                if (EndDate < nextDate)
                    nextDate = EndDate;
                int days = (int)nextDate.Subtract(date).TotalDays;
                float x2 = x1 + (days / (float)totalDays) * totalWidth;

                // Separator for Months
                g.FillRectangle(new SolidBrush(color), new RectangleF(x2, y, 1, totalHeight));

                string text = date.ToString("MMM");
                Font textFont = new Font("Segoe UI", 11, FontStyle.Regular);
                SizeF textSize = g.MeasureString(text, textFont);

                float textX = x1 + (x2 - x1 - textSize.Width) / 2;
                float textY = r.Y + (0.6f * r.Height - textSize.Height) / 2;
                g.DrawString(text, textFont, new SolidBrush(color), textX, textY);

                x1 = x2;
                date = date.AddDays(days);
            }
        }
        #endregion
    }
}


//
//Sparkline\AttendanceSparklineRenderer.cs
//

using FarPoint.Win.Spread;
using System;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.Drawing;
using FarPoint.Win;

namespace GrapeCity.StudentDashboard.Sparkline
{
    /// <summary>
    /// Renderer to draw Attendance Sparkline
    /// </summary>
    public class AttendanceSparklineRenderer : ISparklineRenderer, ISerializable
    {
        #region Properties
        public DateTime StartDate { get; }
        public DateTime EndDate { get; }
        #endregion

        #region ctor
        public AttendanceSparklineRenderer(DateTime startDate, DateTime endDate)
        {
            StartDate = startDate;
            EndDate = endDate;
        }
        #endregion

        #region Public methods
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {

        }

        // Draw vertical line indicating the Absence (upward), Tardiness (downward)
        public void PaintSparkline(Graphics g, Rectangle rectangle, FarPoint.Win.Spread.Sparkline sparkline)
        {
            DistributionSparkline distributionSparkline = sparkline.Data as DistributionSparkline;
            List<DateTime> absentDates = new List<DateTime>();
            List<DateTime> tardyDates = new List<DateTime>();
            
            for (int i = 0; i < distributionSparkline.First.Count; ++i)
            {
                object obj = distributionSparkline.First.GetValue(i);
                DateTime date = DateTime.FromOADate(Convert.ToDouble(obj));
                absentDates.Add(DateTime.FromOADate(Convert.ToDouble(distributionSparkline.First.GetValue(i))));
            }
            for (int i = 0; i < distributionSparkline.Second.Count; ++i)
            {
                tardyDates.Add(DateTime.FromOADate(Convert.ToDouble(distributionSparkline.Second.GetValue(i))));
            }

            float x = rectangle.X + 0.05f * rectangle.Width;
            float y = rectangle.Y + (rectangle.Height / 2);
            float totalWidth = 0.9f * rectangle.Width;
            float height = 0.45f * rectangle.Height;

            // Draw straight horizontal across the cell
            g.FillRectangle(new SolidBrush(Color.FromArgb(180, 180, 180)), new RectangleF(x, y, totalWidth, 1));

            y = rectangle.Y + 0.05f * rectangle.Height;
            int totalDays = (int)EndDate.Subtract(StartDate).TotalDays;
            foreach (DateTime date in absentDates)
            {
                float posX = x + (float)(date.Subtract(StartDate).TotalDays / totalDays) * totalWidth;
                g.FillRectangle(new SolidBrush(Color.DarkOrange), new RectangleF(posX, y, 1, height));
            }

            y = rectangle.Y + 0.5f * rectangle.Height;
            foreach (DateTime date in tardyDates)
            {
                float posX = x + (float)(date.Subtract(StartDate).TotalDays / totalDays) * totalWidth;
                g.FillRectangle(new SolidBrush(Color.DodgerBlue), new RectangleF(posX, y, 1, height));
            }
        }
        #endregion

        #region ISerializable Members
        public bool Serialize(System.Xml.XmlTextWriter w)
        {
            Serializer.SerializeString(GetType().Name, "Name", w);
            return true;
        }
        public bool Deserialize(System.Xml.XmlNodeReader r)
        {
            return true;
        }
        #endregion
    }
}


//
//Sparkline\BulletSparkline.cs
//


using System;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.Xml;
using System.Collections;
using System.ComponentModel;
using System.Globalization;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.Chart;
using FarPoint.Win.Spread.Model;
using FarPoint.Win;
using System.Drawing;
using GrapeCity.CalcEngine;

namespace GrapeCity.StudentDashboard.Sparkline
{
    /// <summary>
    /// BulletSparkline
    /// </summary>
    [Serializable]
    public class BulletSparkline : ISparkline
    {
        #region Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        private int _row;
        private int _column;
        private ISegmentData _measure;
        private ISegmentData _target;
        
        private string _measureFormula;
        private string _targetFormula;
        #endregion

        #region Properties
        public int Row
        {
            get { return _row; }
            set
            {
                int oldValue = _row;
                _row = value;

                PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Row", oldValue, value));
            }
        }

        public int Column
        {
            get { return _column; }
            set
            {
                int oldValue = _column;
                _column = value;

                PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Column", oldValue, value));
            }
        }

        public SheetView Sheet { get; }

        [CellRangeSegmentDataSupport("Measure")]
        public ISegmentData Measure
        {
            get { return _measure; }
            set
            {
                ISegmentData oldValue = _measure;
                _measure = value;
                PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Measure", oldValue, value));
            }
        }

        [CellRangeSegmentDataSupport("Target")]
        public ISegmentData Target
        {
            get { return _target; }
            set
            {
                ISegmentData oldValue = _target;
                _target = value;
                PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Target", oldValue, value));
            }
        }
        #endregion

        #region Events
        public event EventHandler Updated;
        public event PropertyTrackChangedEventHandler PropertyChanged;
        #endregion

        #region ctor
        public BulletSparkline()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
        }

        public BulletSparkline(SheetView sheet, int row, int column, SheetCellRange measure, SheetCellRange target)
        {
            Sheet = sheet;
            _row = row;
            _column = column;
            _measure = new CellRangeSegmentData(measure.SheetView, measure.Row, measure.Column, measure.RowCount, 
                measure.ColumnCount, new ChartDataSetting());
            _target = new CellRangeSegmentData(target.SheetView, target.Row, target.Column, target.RowCount, target.ColumnCount, 
                new ChartDataSetting());

            _measure.DataChanged += SegmentData_DataChanged;
            _target.DataChanged += SegmentData_DataChanged;
        }

        public BulletSparkline(SheetView sheet, int row, int column)
        {
            Sheet = sheet;
            _row = row;
            _column = column;
        }

        protected BulletSparkline(SerializationInfo info, StreamingContext context)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            if (VBt_refL0 == null)
            {
                __ENCList.Add(new WeakReference(this));
            }
            _row = info.GetInt32("Row");
            _column = info.GetInt32("Column");
            _measure = (ISegmentData)info.GetValue("Measure", typeof(ISegmentData));
            _target = (ISegmentData)info.GetValue("Target", typeof(ISegmentData));
        }
        #endregion

        #region Private methods
        private CellRangeSegmentData ParseFormula(FpSpread spread, string formula, bool useR1C1, Hashtable customFunctions,
            Hashtable customNames, ArrayList dataModels)
        {
            IValue value;

            Spreadsheet.Workbook _workbook = (Spreadsheet.Workbook)spread.AsWorkbook();
            SheetView sheet = null;
            int count = spread.Sheets.Count;
            CellRangeSegmentData data = null;

            FormulaEngine formulaParser = _workbook.FormulaEngine;
            formulaParser.EnterContext((IReferenceSource)_workbook.ActiveSheet, 0, 0);
            Expression expression;
            if (formulaParser.Parse(formula, out expression))
            {
                formulaParser.ExitContext();
                spread.ActiveSheet.EvaluateExpression(0, 0, ((IExpressionSupport2)spread.ActiveSheet.Models.Data).ParseFormula(0, 0, formula), out value);

                switch (value.ValueType)
                {
                    case GrapeCity.CalcEngine.ValueType.Reference:
                        var source = value.GetReferenceSource(null);
                        var reference = value.GetReference(null);
                        if (source != null)
                        {
                            for (int i = 0; i < count; i++)
                            {
                                if (source.Name.Equals(spread.Sheets[i].SheetName))
                                {
                                    sheet = spread.Sheets[i];
                                    break;
                                }
                            }
                            data = new CellRangeSegmentData(sheet, reference.Row, reference.Column, reference.Row2 - reference.Row + 1, reference.Column2 - reference.Column + 1, new ChartDataSetting());
                        }
                        break;
                    default:
                        break;
                }
            }
            else
            {
                formulaParser.ExitContext();
            }

            return data;
        }

        private void SegmentData_DataChanged(object sender, SegmentDataChangedEventArgs e)
        {
            Updated?.Invoke(this, EventArgs.Empty);
        }
        #endregion

        #region Public methods
        public object Clone()
        {
            return new BulletSparkline(Sheet, _row, _column, (SheetCellRange)_measure, (SheetCellRange)_target);
        }

        public bool Deserialize(XmlNodeReader r)
        {
            if (!r.MoveToAttribute("Row"))
                return false;
            Row = XmlConvert.ToInt32(r.Value);
            if (!r.MoveToAttribute("Column"))
                return false;
            Column = XmlConvert.ToInt32(r.Value);

            if (!r.MoveToAttribute("MeasureFormula"))
                return false;
            _measureFormula = r.Value;
            if (!r.MoveToAttribute("TargetFormula"))
                return false;
            _targetFormula = r.Value;

            return true;
        }

        public void ReloadFormula(FpSpread spread, SheetView ownerSheetView)
        {
            ArrayList dataModels = new ArrayList();
            foreach (SheetView sheet in spread.Sheets)
            {
                dataModels.Add(sheet.Models.Data);
            }
            if (!string.IsNullOrEmpty(_measureFormula))
                _measure = ParseFormula(spread, _measureFormula, false, null, null, dataModels);
            if (!string.IsNullOrEmpty(_targetFormula))
                _target = ParseFormula(spread, _targetFormula, false, null, null, dataModels);
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Row", Row);
            info.AddValue("Column", Column);
            info.AddValue("Measure", Measure);
            info.AddValue("Target", Target);
        }

        public object GetSparkLineData()
        {
            List<ISegmentData> values = new List<ISegmentData>
            {
                Measure,
                Target
            };
            return values;
        }

        public bool Serialize(XmlTextWriter w)
        {
            try
            {
                w.WriteAttributeString("Row", Row.ToString(CultureInfo.InvariantCulture));
                w.WriteAttributeString("Column", Column.ToString(CultureInfo.InvariantCulture));

                CellRangeSegmentData measure = (CellRangeSegmentData)_measure;
                CellRangeSegmentData target = (CellRangeSegmentData)_target;

                _measureFormula = measure.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    measure.Row, measure.Column, measure.Row + measure.RowCount - 1, measure.Column + measure.ColumnCount - 1));

                _targetFormula = target.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    target.Row, target.Column, target.Row + target.RowCount - 1, target.Column + target.ColumnCount - 1));
                
                w.WriteAttributeString("MeasureFormula", _measureFormula);
                w.WriteAttributeString("TargetFormula", _targetFormula);
            }
            catch
            {
                return false;
            }
            return true;
        }
        #endregion
    }

    /// <summary>
    /// Sparkline group for BulletSparkline
    /// </summary>
    [Serializable]
    public class BulletSparklineGroup : ISparklineGroup, ISheetCellRangeSupport
    {
        #region Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        private List<ISparkline> _list;
        private BulletSparklineRenderer _renderer;
        private BulletSparklineSetting _setting;
        #endregion

        #region ctor
        public BulletSparklineGroup()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            Renderer = new BulletSparklineRenderer();
            _list = new List<ISparkline>();
            Setting = new BulletSparklineSetting();
        }

        protected BulletSparklineGroup(BulletSparklineRenderer renderer, BulletSparklineSetting setting, List<ISparkline> list)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            _renderer = new BulletSparklineRenderer();
            _setting = new BulletSparklineSetting();
            _list = new List<ISparkline>();
            _renderer = renderer;
            _setting = setting;
            _list.AddRange(list);
        }

        protected BulletSparklineGroup(SerializationInfo info, StreamingContext context)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            _renderer = new BulletSparklineRenderer();
            _setting = new BulletSparklineSetting();
            _list = new List<ISparkline>();
            _renderer = (BulletSparklineRenderer)info.GetValue("Renderer", typeof(BulletSparklineRenderer));
            _setting = (BulletSparklineSetting)info.GetValue("Setting", typeof(BulletSparklineSetting));
            _list = (List<ISparkline>)info.GetValue("List", typeof(List<BulletSparkline>));
        }
        #endregion

        #region Properties
        public ISparkline this[int index]
        {
            get
            {
                if ((index < 0) || (index >= _list.Count))
                {
                    string[] VBt_arrayS0 = new string[] { "Invalid index: ", index.ToString(), " (must be between 0 and ", (_list.Count - 1).ToString(), ")" };
                    throw new IndexOutOfRangeException($"Invalid index: {index.ToString()} (must be between 0 and {_list.Count - 1})");
                }
                return _list[index];
            }
        }

        public ISparklineSetting Setting
        {
            get { return _setting; }
            set
            {
                if (!(value is BulletSparklineSetting))
                {
                    throw new ArgumentException("Invalid setting type - must be BulletSparklineSetting");
                }
                _setting = (BulletSparklineSetting)value;
                _setting.PropertyChanged += Setting_PropertyChanged;
                FireUpdated();
            }
        }

        public ISparklineRenderer Renderer
        {
            get { return _renderer; }
            set
            {
                if (!(value is BulletSparklineRenderer))
                {
                    throw new ArgumentException("Invalid renderer type - must be BulletSparklineRenderer");
                }
                _renderer = (BulletSparklineRenderer)value;
                Updated?.Invoke(this, EventArgs.Empty);
            }
        }

        public int Count
        {
            get { return _list.Count; }
        }

        public bool IsReadOnly
        {
            get { return false; }
        }
        #endregion

        #region ISheetCellRangeSupport Members

        public event ReloadSheetCellRangeEventHandler FormulaChanged;

        public void ReLoadFormula(FpSpread spread, SheetView ownerSheetView)
        {
            foreach (BulletSparkline sp in this)
            {
                sp.ReloadFormula(spread, ownerSheetView);
            }
        }

        public bool ReferenceError
        {
            get { return false; }
        }

        #endregion

        #region Events
        public event EventHandler Updated;
        public event EventHandler ClearItems;
        public event ItemEventHandler ItemUpdated;
        public event ItemEventHandler ItemAdded;
        public event ItemEventHandler ItemRemoved;
        #endregion

        #region Private methods
        private void Setting_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            FireUpdated();
        }

        private void FireUpdated()
        {
            Updated?.Invoke(this, EventArgs.Empty);
        }

        private void SparkLine_Updated(object sender, EventArgs e)
        {
            Updated?.Invoke(this, EventArgs.Empty);
        }
        #endregion

        #region Public methods
        public void Add(ISparkline item)
        {
            _list.Add(item);
            item.Updated += SparkLine_Updated;
            ItemAdded?.Invoke(this, new ItemEventArgs(item));
        }

        public void Clear()
        {
            _list.Clear();
            ClearItems?.Invoke(this, EventArgs.Empty);
        }

        public object Clone()
        {
            return CloneSetting();
        }

        public ISparklineGroup CloneSetting()
        {
            return new BulletSparklineGroup(_renderer, _setting, _list);
        }

        public bool Contains(ISparkline item)
        {
            return _list.Contains(item);
        }

        public void CopyTo(ISparkline[] array, int arrayIndex)
        {
            _list.CopyTo(array, arrayIndex);
        }
        
        public bool Deserialize(XmlNodeReader r)
        {
            bool readNext = true;
            _list = new List<ISparkline>();
            object o;
            if (r.IsEmptyElement)
                return true;
            while (true)
            {
                if (readNext)
                {
                    if (!r.Read())
                        break;
                }
                else
                    readNext = true;
                switch (r.NodeType)
                {
                    case XmlNodeType.Element:
                        if (r.Name.Equals("Renderer"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(BulletSparklineRenderer));
                            if (o != null)
                                _renderer = (BulletSparklineRenderer)o;
                            readNext = false;
                        }
                        else if (r.Name.Equals("Setting"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(BulletSparklineSetting));
                            if (o != null)
                                Setting = (BulletSparklineSetting)o;
                            readNext = false;
                        }
                        else if (r.Name.Equals("Sparkline"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(BulletSparkline));
                            if (o != null)
                                Add((BulletSparkline)o);
                            readNext = false;
                        }
                        break;
                }
            }
            return true;
        }

        public IEnumerator<ISparkline> GetEnumerator()
        {
            return _list.GetEnumerator();
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("List", _list);
            info.AddValue("Renderer", _renderer);
            info.AddValue("Setting", _setting);
        }

        public bool Remove(ISparkline item)
        {
            bool retValue = _list.Remove(item);
            ItemRemoved?.Invoke(this, new ItemEventArgs(item));

            item.Updated -= SparkLine_Updated;
            return retValue;
        }

        public bool Serialize(XmlTextWriter w)
        {
            w.WriteStartElement("Renderer");
            w.WriteAttributeString("Class", _renderer.GetType().FullName);
            w.WriteAttributeString("Assembly", _renderer.GetType().Assembly.FullName);
            _renderer.Serialize(w);
            w.WriteEndElement();

            w.WriteStartElement("Setting");
            w.WriteAttributeString("Class", _setting.GetType().FullName);
            w.WriteAttributeString("Assembly", _setting.GetType().Assembly.FullName);
            _setting.Serialize(w);
            w.WriteEndElement();
            int count = _list.Count;
            for (int i = 0; i < count; i++)
            {
                w.WriteStartElement("Sparkline");
                w.WriteAttributeString("Class", _list[i].GetType().FullName);
                w.WriteAttributeString("Assembly", _list[i].GetType().Assembly.FullName);
                _list[i].Serialize(w);
                w.WriteEndElement();
            }
            return true;
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            return GetEnumerator();
        }
        #endregion
    }

    /// <summary>
    /// Sparkline Setting for BulletSparkline
    /// </summary>
    public class BulletSparklineSetting : ISparklineSetting
    {
        #region Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        private double _tickUnit;
        private Color _colorScheme;
        private bool _vertical;
        #endregion

        #region Events
        public event PropertyChangedEventHandler PropertyChanged;
        #endregion

        #region ctor
        public BulletSparklineSetting()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            _tickUnit = 0;
            _colorScheme = Color.FromArgb(247, 204, 174);
            _vertical = false;
        }

        protected BulletSparklineSetting(SerializationInfo info, StreamingContext context)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            _tickUnit = info.GetDouble("TickUnit");
            _colorScheme = (Color)info.GetValue("ColorScheme", typeof(Color));
            _vertical = info.GetBoolean("Vertical");
        }

        public BulletSparklineSetting(double tickUnit, Color colorScheme, bool vertical)
        {
            List<WeakReference> VBt_refL0 = new List<WeakReference>();
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            _tickUnit = tickUnit;
            _colorScheme = colorScheme;
            _vertical = vertical;
        }
        #endregion

        #region Properties
        public double TickUnit
        {
            get { return _tickUnit; }
            set
            {
                _tickUnit = value;
                PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("TickUnit"));
            }
        }

        public Color ColorScheme
        {
            get { return _colorScheme; }
            set
            {
                _colorScheme = value;
                PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("ColorScheme"));
            }
        }

        public bool Vertical
        {
            get { return _vertical; }
            set
            {
                _vertical = value;
                PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("Vertical"));
            }
        }
        #endregion

        #region Public methods
        public bool Deserialize(XmlNodeReader r)
        {
            if (!r.MoveToAttribute("TickUnit"))
                return false;
            _tickUnit = XmlConvert.ToDouble("TickUnit");

            ColorConverter con = new ColorConverter();
            if (!r.MoveToAttribute("ColorScheme"))
                return false;
            _colorScheme = (Color)con.ConvertFromInvariantString(r.Value);

            if (!r.MoveToAttribute("Vertical"))
                return false;
            _vertical = XmlConvert.ToBoolean(r.Value);

            return true;
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("TickUnit", _tickUnit);
            info.AddValue("ColorScheme", _colorScheme);
            info.AddValue("Vertical", _vertical);
        }

        public bool Serialize(XmlTextWriter w)
        {
            ColorConverter colorConverter = new ColorConverter();
            w.WriteAttributeString("TickUnit", _tickUnit.ToString());
            w.WriteAttributeString("ColorScheme", colorConverter.ConvertToInvariantString(_colorScheme));
            w.WriteAttributeString("Vertical", _vertical.ToString());
            return true;
        }
        #endregion
    }

    [AttributeUsage(AttributeTargets.Property)]
    internal class CellRangeSegmentDataSupportAttribute : Attribute
    {
        private bool _multiCell;
        private string _text;
        
        public CellRangeSegmentDataSupportAttribute(string text, bool multiCell = false)
        {
            _text = text;
            _multiCell = multiCell;
        }

        public string Text
        {
            get { return _text; }
        }

        public bool MultiCell
        {
            get { return _multiCell; }
        }
    }
}


//
//Sparkline\BulletSparklineRenderer.cs
//


using FarPoint.Win;
using FarPoint.Win.Spread;
using GrapeCity.StudentDashboard.Sparkline;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Runtime.Serialization;
using System.Text;
using System.Threading.Tasks;

namespace GrapeCity.StudentDashboard.Sparkline
{
    public class BulletSparklineRenderer : ISparklineRenderer, ISerializable
    {
        #region Fields
        private int max;
        private int good;
        private int bad;
        private bool drawForecast;
        #endregion

        public BulletSparklineRenderer() { }
        public BulletSparklineRenderer(int max, int good, int bad, bool drawForecast = false)
        {
            this.max = max;
            this.good = good;
            this.bad = bad;
            this.drawForecast = drawForecast;
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            
        }

        public void PaintSparkline(Graphics g, Rectangle rectangle, FarPoint.Win.Spread.Sparkline sparkline)
        {
            BulletSparkline data = (BulletSparkline)sparkline.Data;
            int measure = Convert.ToInt32(data.Measure.GetValue(0));

            int target = 0;
            for (int i = 0; i < data.Target.Count; ++i)
                target += Convert.ToInt32(data.Target.GetValue(i));
            target = target / data.Target.Count;

            int? forecast = null;
            if (drawForecast)
                forecast = (int)(1.1 * measure); 

            BulletSparklineSetting setting = (BulletSparklineSetting)sparkline.Setting;
            Color colorScheme = setting.ColorScheme;
            double tickUnit = setting.TickUnit;

            RectangleF rect = new RectangleF(rectangle.X + 0.05f * rectangle.Width, rectangle.Y + 0.2f * rectangle.Height,
                0.9f * rectangle.Width, 0.6f * rectangle.Height);

            if (setting.Vertical)
                PaintVerticalSparkline(g, rect, colorScheme, tickUnit, measure, target, max, good, bad, forecast);
            else
                PaintHorizontalSparkline(g, rect, colorScheme, tickUnit, measure, target, max, good, bad, forecast);
        }

        private void PaintHorizontalSparkline(Graphics g, RectangleF rectangle, Color colorScheme, double tickUnit, int measure,
            int target, int max, int good, int bad, int? forecast)
        {
            g.FillRectangle(new SolidBrush(colorScheme), rectangle);
            g.FillRectangle(new SolidBrush(Darker(colorScheme, -15)), rectangle.X, rectangle.Y, good / (float)max * rectangle.Width,
                rectangle.Height);
            g.FillRectangle(new SolidBrush(Darker(colorScheme, -30)), rectangle.X, rectangle.Y, bad / (float)max * rectangle.Width,
                rectangle.Height);

            Brush brush = new SolidBrush(Color.Black);
            if (measure > good)
                brush = new SolidBrush(Color.FromArgb(135, 44, 65));
            g.FillRectangle(new SolidBrush(Color.Black), rectangle.X, rectangle.Y + 0.35f * rectangle.Height,
                measure / (float)max * rectangle.Width, 0.3f * rectangle.Height);

            if (forecast.HasValue)
                g.FillRectangle(new SolidBrush(Color.CadetBlue), rectangle.X, rectangle.Y + 0.45f * rectangle.Height,
                    forecast.Value / (float)max * rectangle.Width, 0.1f * rectangle.Height);

            g.FillRectangle(new SolidBrush(Color.Red), rectangle.X + target * rectangle.Width / max, rectangle.Y,
                Math.Min(2, 0.005f * rectangle.Width), rectangle.Height);
        }

        private void PaintVerticalSparkline(Graphics g, RectangleF rectangle, Color colorScheme, double tickUnit, int measure, 
            int target, int max, int good, int bad, int? forecast)
        {
            g.FillRectangle(new SolidBrush(colorScheme), rectangle);
            g.FillRectangle(new SolidBrush(Darker(colorScheme, 50)), rectangle.X, rectangle.Y, rectangle.Width,
                good / (float)max * rectangle.Height);
            g.FillRectangle(new SolidBrush(Darker(colorScheme, 100)), rectangle.X, rectangle.Y, rectangle.Width,
                bad / (float)max * rectangle.Height);

            g.FillRectangle(new SolidBrush(Color.Black), rectangle.X + 0.41f * rectangle.Width, rectangle.Y, 0.18f * rectangle.Width,
                measure / (float)max * rectangle.Height);

            if (forecast.HasValue)
                g.FillRectangle(new SolidBrush(Color.CadetBlue), rectangle.X + 0.47f * rectangle.Width, rectangle.Y, 0.06f *
                    rectangle.Width, forecast.Value / (float)max * rectangle.Height);

            g.FillRectangle(new SolidBrush(Color.Red), rectangle.X, target / (float)max * rectangle.Height, rectangle.Width,
                0.01f * rectangle.Height);
        }

        private Color Darker(Color color, int delta)
        {
            int r = color.R + delta;
            int g = color.G + delta;
            int b = color.B + delta;

            if (r > 0xff)
                r = 0xff;
            if (r < 0)
                r = 0;
            if (g > 0xff)
                g = 0xff;
            if (g < 0)
                g = 0;
            if (b > 0xff)
                b = 0xff;
            if (b < 0)
                b = 0;

            return Color.FromArgb(r, g, b);
        }

        #region ISerializable Members
        public bool Serialize(System.Xml.XmlTextWriter w)
        {
            Serializer.SerializeString("BulletSparklineRenderer", "Name", w);
            return true;
        }
        public bool Deserialize(System.Xml.XmlNodeReader r)
        {
            return true;
        }
        #endregion
    }
}


//
//Sparkline\DistributionSparkline.cs
//


using System;
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.Xml;
using System.Collections;
using System.ComponentModel;
using System.Globalization;
using FarPoint.Win.Spread;
using FarPoint.Win.Spread.Chart;
using FarPoint.Win.Spread.Model;
using FarPoint.Win;
using GrapeCity.CalcEngine;

namespace GrapeCity.StudentDashboard.Sparkline
{
    /// <summary>
    /// Distribution Sparkline
    /// </summary>
    [Serializable]
    public class DistributionSparkline : ISparkline
    {
        #region Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        private int _row;
        private int _column;
        private SheetView _sheet;
        private ISegmentData _first;
        private ISegmentData _second;

        private string _firstFormula;
        private string _secondFormula;
        #endregion

        #region Properties
        public int Row
        {
            get { return _row; }
            set
            {
                int oldValue = _row;
                _row = value;

                PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Row", oldValue, value));
            }
        }

        public int Column
        {
            get { return _column; }
            set
            {
                int oldValue = _column;
                _column = value;

                PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Column", oldValue, value));
            }
        }

        public SheetView Sheet
        {
            get { return _sheet; }
        }

        [CellRangeSegmentDataSupport("First")]
        public ISegmentData First
        {
            get { return _first; }
            set
            {
                ISegmentData oldValue = _first;
                _first = value;
                PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("First", oldValue, value));
            }
        }

        [CellRangeSegmentDataSupport("Second")]
        public ISegmentData Second
        {
            get { return _second; }
            set
            {
                ISegmentData oldValue = _second;
                _second = value;
                PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Second", oldValue, value));
            }
        }
        #endregion

        #region Events
        public event EventHandler Updated;
        public event PropertyTrackChangedEventHandler PropertyChanged;
        #endregion

        #region ctor
        public DistributionSparkline()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
        }

        public DistributionSparkline(SheetView sheet, int row, int column, SheetCellRange first, 
            SheetCellRange second = null)
        {
            _sheet = sheet;
            _row = row;
            _column = column;
            _first = new CellRangeSegmentData(first.SheetView, first.Row, first.Column,
                first.RowCount, first.ColumnCount, new ChartDataSetting());

            _first.DataChanged += SegmentData_DataChanged;

            if (second != null)
            {
                _second = new CellRangeSegmentData(second.SheetView, second.Row, second.Column,
                    second.RowCount, second.ColumnCount, new ChartDataSetting());
                _second.DataChanged += SegmentData_DataChanged;
            }
        }

        public DistributionSparkline(SheetView sheet, int row, int column)
        {
            _sheet = sheet;
            _row = row;
            _column = column;
        }
        #endregion

        #region Private methods
        private void SegmentData_DataChanged(object sender, SegmentDataChangedEventArgs e)
        {
            Updated?.Invoke(this, EventArgs.Empty);
        }

        private CellRangeSegmentData ParseFormula(FpSpread spread, string formula, bool useR1C1, Hashtable customFunctions,
            Hashtable customNames, ArrayList dataModels)
        {
            IValue value;
            Spreadsheet.Workbook _workbook = (Spreadsheet.Workbook)spread.AsWorkbook();
            SheetView sheet = null;
            int count = spread.Sheets.Count;
            CellRangeSegmentData data = null;

            FormulaEngine formulaParser = _workbook.FormulaEngine;
            formulaParser.EnterContext((IReferenceSource)_workbook.ActiveSheet, 0, 0);
            Expression expression;
            if (formulaParser.Parse(formula, out expression))
            {
                formulaParser.ExitContext();
                spread.ActiveSheet.EvaluateExpression(0, 0, ((IExpressionSupport2)spread.ActiveSheet.Models.Data).ParseFormula(0, 0, formula), out value);

                switch (value.ValueType)
                {
                    case GrapeCity.CalcEngine.ValueType.Reference:
                        var source = value.GetReferenceSource(null);
                        var reference = value.GetReference(null);
                        if (source != null)
                        {
                            for (int i = 0; i < count; i++)
                            {
                                if (source.Name.Equals(spread.Sheets[i].SheetName))
                                {
                                    sheet = spread.Sheets[i];
                                    break;
                                }
                            }
                            data = new CellRangeSegmentData(sheet, reference.Row, reference.Column, reference.Row2 - reference.Row + 1, reference.Column2 - reference.Column + 1, new ChartDataSetting());
                        }
                        break;
                    default:
                        break;
                }
            }
            else
            {
                formulaParser.ExitContext();
            }

            return data;
        }
        #endregion

        #region Public methods
        public object Clone()
        {
            return new DistributionSparkline(_sheet, _row, _column, (SheetCellRange)_first,
                (SheetCellRange)_second);
        }

        public bool Deserialize(XmlNodeReader r)
        {
            if (!r.MoveToAttribute("Row"))
                return false;
            Row = XmlConvert.ToInt32(r.Value);
            if (!r.MoveToAttribute("Column"))
                return false;
            Column = XmlConvert.ToInt32(r.Value);

            if (!r.MoveToAttribute("FirstFormula"))
                return false;
            _firstFormula = r.Value;
            if (!r.MoveToAttribute("SecondFormula"))
                return false;
            _secondFormula = r.Value;

            return true;
        }

        public void ReloadFormula(FpSpread spread, SheetView ownerSheetView)
        {
            ArrayList dataModels = new ArrayList();
            foreach (SheetView sheet in spread.Sheets)
            {
                dataModels.Add(sheet.Models.Data);
            }
            if (!string.IsNullOrEmpty(_firstFormula))
                _first = ParseFormula(spread, _firstFormula, false, null, null, dataModels);
            if (!string.IsNullOrEmpty(_secondFormula))
                _second = ParseFormula(spread, _secondFormula, false, null, null, dataModels);
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("Row", Row);
            info.AddValue("Column", Column);
            info.AddValue("First", First);
            info.AddValue("Second", Second);
        }

        public object GetSparkLineData()
        {
            List<ISegmentData> values = new List<ISegmentData>
            {
                First,
                Second
            };
            return values;
        }

        public bool Serialize(XmlTextWriter w)
        {
            try
            {
                w.WriteAttributeString("Row", Row.ToString(CultureInfo.InvariantCulture));
                w.WriteAttributeString("Column", Column.ToString(CultureInfo.InvariantCulture));

                CellRangeSegmentData first = (CellRangeSegmentData)_first;
                CellRangeSegmentData second = (CellRangeSegmentData)_second;

                _firstFormula = first.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    first.Row, first.Column, first.Row + first.RowCount - 1, first.Column + first.ColumnCount - 1));

                if (second != null)
                {
                    _secondFormula = first.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                   second.Row, second.Column, second.Row + second.RowCount - 1, second.Column + second.ColumnCount - 1));

                }

                w.WriteAttributeString("FirstFormula", _firstFormula);
                w.WriteAttributeString("SecondFormula", _secondFormula);
            }
            catch
            {
                return false;
            }
            return true;
        }
        #endregion
    }

    /// <summary>
    /// Sparkline group for Distribution Sparkline
    /// </summary>
    [Serializable]
    public class DistributionSparklineGroup : ISparklineGroup, ISheetCellRangeSupport
    {
        #region Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        private List<ISparkline> _list;
        private ISparklineRenderer _renderer;
        private DistributionSparklineSetting _setting;
        #endregion

        #region ctor
        public DistributionSparklineGroup()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            Renderer = new DistributionSparklineRenderer();
            _list = new List<ISparkline>();
            Setting = new DistributionSparklineSetting();
        }

        protected DistributionSparklineGroup(ISparklineRenderer renderer, DistributionSparklineSetting setting, List<ISparkline> list)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            _renderer = new DistributionSparklineRenderer();
            _setting = new DistributionSparklineSetting();
            _list = new List<ISparkline>();
            _renderer = renderer;
            _setting = setting;
            _list.AddRange(list);
        }

        protected DistributionSparklineGroup(SerializationInfo info, StreamingContext context)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            _renderer = new DistributionSparklineRenderer();
            _setting = new DistributionSparklineSetting();
            _list = new List<ISparkline>();
            _renderer = (DistributionSparklineRenderer)info.GetValue("Renderer", typeof(DistributionSparklineRenderer));
            _setting = (DistributionSparklineSetting)info.GetValue("Setting", typeof(DistributionSparklineSetting));
            _list = (List<ISparkline>)info.GetValue("List", typeof(List<DistributionSparkline>));
        }
        #endregion

        #region Properties
        public ISparkline this[int index]
        {
            get
            {
                if ((index < 0) || (index >= _list.Count))
                {
                    string[] VBt_arrayS0 = new string[] { "Invalid index: ", index.ToString(), " (must be between 0 and ", (_list.Count - 1).ToString(), ")" };
                    throw new IndexOutOfRangeException($"Invalid index: {index.ToString()} (must be between 0 and {_list.Count - 1})");
                }
                return _list[index];
            }
        }

        public ISparklineSetting Setting
        {
            get { return _setting; }
            set
            {
                if (!(value is DistributionSparklineSetting))
                {
                    throw new ArgumentException("Invalid setting type - must be DistributionSparklineSetting");
                }
                _setting = (DistributionSparklineSetting)value;
                _setting.PropertyChanged += Setting_PropertyChanged;
                FireUpdated();
            }
        }

        public ISparklineRenderer Renderer
        {
            get { return _renderer; }
            set
            {
                if (!(value is DistributionSparklineRenderer || 
                    value is AttendanceSparklineRenderer))
                {
                    throw new ArgumentException("Invalid renderer type - must be DistributionSparklineRenderer, " +
                        "ContrastSparklineRenderer, or AttendanceSparklineRenderer");
                }
                if (value is DistributionSparklineRenderer)
                    _renderer = (DistributionSparklineRenderer)value;
                else
                    _renderer = (AttendanceSparklineRenderer)value;
                Updated?.Invoke(this, EventArgs.Empty);
            }
        }
        public int Count
        {
            get { return _list.Count; }
        }

        public bool IsReadOnly
        {
            get { return false; }
        }
        #endregion

        #region ISheetCellRangeSupport Members

        public event ReloadSheetCellRangeEventHandler FormulaChanged;

        public void ReLoadFormula(FpSpread spread, SheetView ownerSheetView)
        {
            foreach (DistributionSparkline sp in this)
            {
                sp.ReloadFormula(spread, ownerSheetView);
            }
        }

        public bool ReferenceError
        {
            get { return false; }
        }

        #endregion

        #region Events
        public event EventHandler Updated;
        public event EventHandler ClearItems;
        public event ItemEventHandler ItemUpdated;
        public event ItemEventHandler ItemAdded;
        public event ItemEventHandler ItemRemoved;
        #endregion

        #region Private methods
        private void Setting_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            FireUpdated();
        }

        private void FireUpdated()
        {
            Updated?.Invoke(this, EventArgs.Empty);
        }

        private void SparkLine_Updated(object sender, EventArgs e)
        {
            Updated?.Invoke(this, EventArgs.Empty);
        }
        #endregion

        #region Public methods
        public void Add(ISparkline item)
        {
            _list.Add(item);
            item.Updated += SparkLine_Updated;
            ItemAdded?.Invoke(this, new ItemEventArgs(item));
        }

        public void Clear()
        {
            _list.Clear();
            ClearItems?.Invoke(this, EventArgs.Empty);
        }

        public object Clone()
        {
            return CloneSetting();
        }

        public ISparklineGroup CloneSetting()
        {
            return new DistributionSparklineGroup(_renderer, _setting, _list);
        }

        public bool Contains(ISparkline item)
        {
            return _list.Contains(item);
        }

        public void CopyTo(ISparkline[] array, int arrayIndex)
        {
            _list.CopyTo(array, arrayIndex);
        }

        public bool Deserialize(XmlNodeReader r)
        {
            bool readNext = true;
            _list = new List<ISparkline>();
            object o;
            if (r.IsEmptyElement)
                return true;
            while (true)
            {
                if (readNext)
                {
                    if (!r.Read())
                        break;
                }
                else
                    readNext = true;
                switch (r.NodeType)
                {
                    case XmlNodeType.Element:
                        if (r.Name.Equals("Renderer"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(DistributionSparklineRenderer));
                            if (o != null)
                                _renderer = (DistributionSparklineRenderer)o;
                            readNext = false;
                        }
                        else if (r.Name.Equals("Setting"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(DistributionSparklineSetting));
                            if (o != null)
                                Setting = (DistributionSparklineSetting)o;
                            readNext = false;
                        }
                        else if (r.Name.Equals("Sparkline"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(DistributionSparkline));
                            if (o != null)
                                Add((DistributionSparkline)o);
                            readNext = false;
                        }
                        break;
                }
            }
            return true;
        }

        public IEnumerator<ISparkline> GetEnumerator()
        {
            return _list.GetEnumerator();
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("List", _list);
            info.AddValue("Renderer", _renderer);
            info.AddValue("Setting", _setting);
        }

        public bool Remove(ISparkline item)
        {
            bool retValue = _list.Remove(item);
            ItemRemoved?.Invoke(this, new ItemEventArgs(item));

            item.Updated -= SparkLine_Updated;
            return retValue;
        }

        public bool Serialize(XmlTextWriter w)
        {
            w.WriteStartElement("Renderer");
            w.WriteAttributeString("Class", _renderer.GetType().FullName);
            w.WriteAttributeString("Assembly", _renderer.GetType().Assembly.FullName);
            (_renderer as DistributionSparklineRenderer).Serialize(w);
            w.WriteEndElement();

            w.WriteStartElement("Setting");
            w.WriteAttributeString("Class", _setting.GetType().FullName);
            w.WriteAttributeString("Assembly", _setting.GetType().Assembly.FullName);
            _setting.Serialize(w);
            w.WriteEndElement();
            int count = _list.Count;
            for (int i = 0; i < count; i++)
            {
                w.WriteStartElement("Sparkline");
                w.WriteAttributeString("Class", _list[i].GetType().FullName);
                w.WriteAttributeString("Assembly", _list[i].GetType().Assembly.FullName);
                _list[i].Serialize(w);
                w.WriteEndElement();
            }
            return true;
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            return GetEnumerator();
        }
        #endregion
    }

    /// <summary>
    /// Sparkline Setting for Distribution Sparkline
    /// </summary>
    public class DistributionSparklineSetting : ISparklineSetting
    {
        #region Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        #endregion

        #region Events
        public event PropertyChangedEventHandler PropertyChanged;
        #endregion

        #region ctor
        public DistributionSparklineSetting()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
        }

        protected DistributionSparklineSetting(SerializationInfo info, StreamingContext context)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
        }
        #endregion

        #region Public methods
        public bool Deserialize(XmlNodeReader r)
        {
            return true;
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            return;
        }

        public bool Serialize(XmlTextWriter w)
        {
            return true;
        }
        #endregion
    }
}


//
//DistributionSparklineRenderer.cs
//


using FarPoint.Win.Spread;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.Drawing;
using FarPoint.Win;

namespace GrapeCity.StudentDashboard.Sparkline
{
    /// <summary>
    /// Renderer for Distribution Sparkline
    /// </summary>
    public class DistributionSparklineRenderer : ISparklineRenderer, ISerializable
    {
        #region Public methods
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {

        }

        // Draw distribution density of values
        public void PaintSparkline(Graphics g, Rectangle rectangle, FarPoint.Win.Spread.Sparkline sparkline)
        {
            DistributionSparkline distributionSparkline = sparkline.Data as DistributionSparkline;
            List<int> values = new List<int>();

            for (int i = 0; i < distributionSparkline.First.Count; ++i)
            {
                values.Add(Convert.ToInt32(distributionSparkline.First.GetValue(i)));
            }
            
            int min = values.Min();
            int max = values.Max();
            int range = max - min;

            float x = rectangle.X + 0.1f * rectangle.Width;
            float y = rectangle.Y + rectangle.Height / 2;
            float totalWidth = 0.8f * rectangle.Width;

            foreach (int value in values)
            {
                float x2 = x + (value - min) / (float)range * totalWidth;
                g.FillPie(new SolidBrush(Color.Gray), new Rectangle((int)x2 - 2, (int)y - 2, 3, 3), 0, 360);
                //g.FillEllipse(new SolidBrush(Color.Gray), new RectangleF(x2 - 1.5f, y - 1.5f, 3, 3));
            }
        }
        #endregion

        #region ISerializable Members
        public bool Serialize(System.Xml.XmlTextWriter w)
        {
            Serializer.SerializeString(GetType().Name, "Name", w);
            return true;
        }
        public bool Deserialize(System.Xml.XmlNodeReader r)
        {
            return true;
        }
        #endregion
    }
}
